﻿use Workflow
go
--创建团队属性表
create table Teams
(
	TeamId int identity(0,1) primary key,
	TeamName nvarchar(50) not null
)
go
--创建角色属性表
create table Roles
(
	RoleId int identity(0,1) primary key,
	RoleName nvarchar(50) not null,
	BelongTeam int references Teams(TeamId) not null
)
go
--创建用户信息表
create table UserInfoes
(
	UserName nvarchar(50) not null primary key,
	DisplayName nvarchar(50) not null,
	Email nvarchar(50)
)
go
--创建工作流模板表
create table WorkflowMoulds
(
	WorkParentId int identity(1,1) primary key,
	WorkName nvarchar(50) not null,
	NodeCount int not null,--节点数量
	CreateTeamId int references Teams(TeamId),
	CreateRoleId int references Roles(RoleId),
	TableName nvarchar(20),
	ReaderTeamId int references Teams(TeamId),
	ReaderRoleId int references Roles(RoleId),
	WebName nvarchar(50)
)
go
--创建用户团队角色关系表
create table UserTeamRoles
(
	Id int identity(1,1) primary key,
	UserName nvarchar(50) references UserInfoes(UserName),
	TeamId int references Teams(TeamId),
	RoleId int references Roles(RoleId),
	WorkMouldId int references WorkflowMoulds(WorkParentId)
)
go

--创建工作流子节点模板表
create table WorkflowChildMoulds
(
	WorkChildId int identity(1,1) primary key,
	WorkParentId int references WorkflowMoulds(WorkParentId),
	NodeNo int not null,--节点序号
	IsSingleNode int not null,
	ChildNodeNo int not null,--子节点序号
	TeamId int references Teams(TeamId),
	RoleId int references Roles(RoleId),
	UserName nvarchar(50) references UserInfoes(UserName),
	NodeName nvarchar(50) 
)
use Workflow
go
--创建工作流状态表
create table WorkflowStatuses
(
	WorkParentId int identity(1,1) primary key,
	CreaterUserName nvarchar(50) references UserInfoes(UserName),
	WorkMouldId int references WorkflowMoulds(WorkParentId),
	WorkName nvarchar(50) not null,
	NodeCount int not null,
	StatusDefine int default(0) not null,
	StatusNo int default(0) not null,
	CreateTime datetime default(getdate()),
	CompleteTime datetime,
	CreaterTeamId int references Teams(TeamId),
	CreaterRoleId int references Roles(RoleId),
	ReaderTeamId int references Teams(TeamId),
	ReaderRoleId int references Roles(RoleId),
	TableName nvarchar(20),
	WebName nvarchar(50)
)
--创建工作流子节点状态表
create table WorkflowChildStatuses
(
	WorkChildId int identity(1,1) primary key,
	WorkParentId int references WorkflowStatuses(WorkParentId),
	NodeNo int not null,--节点序号
	IsSingleNode int not null,
	ChildNodeNo int not null,--子节点序号
	TeamId int references Teams(TeamId),
	RoleId int references Roles(RoleId),
	StatusNo int not null,
	LastChangeTime datetime,
	UserName nvarchar(50) references UserInfoes(UserName),
	NodeName nvarchar(50) 
)
go
use Workflow
--创建工作流状态Log日志表
create table WorkflowLog
(
	Id int identity(1,1) primary key,
	UserName nvarchar(50) references UserInfoes(UserName),
	WorkParentId int references WorkflowStatuses(WorkParentId),
	WorkMouldId int references WorkflowMoulds(WorkParentId),
	WorkChildId int references WorkflowChildStatuses(WorkChildId),
	StatusNo int not null,
	ChildNodeNo int not null,
	ChangeTime datetime not null,
	Type1 nvarchar(10) not null 
)

go
use Workflow
--创建上传文件信息表
create table WorkflowUpLoad
(
	Id int identity(1,1) primary key,
	WorkParentId int references WorkflowStatuses(WorkParentId),
	UpLoadTime datetime default(getdate()),
	FileAddress nvarchar(max)
)

use Workflow
go
create table BaoJia
(
	Id int identity(1,1) primary key,
	WorkParentId int,
	Number nvarchar(50),
	Customer nvarchar(50),
	Class nvarchar(50),
	CostType nvarchar(50),
	Requirement nvarchar(50),
	Sales nvarchar(50),
	Success nvarchar(1) 
)
